import pandas as pd
import json

df = pd.read_excel('Upload payment proof_GC_20250401-2.xlsx').fillna('')
rules = []
last_pm = last_amt = last_hr = None
last_rule = None

for i, row in df.iterrows():
    pm = row['Payment Method'].strip() or last_pm or ''
    amt = row['Total payment amount (sum of all transactions for per payor)'].strip() or last_amt or ''
    hr = row['High Risk Product'].strip() or last_hr or ''
    doc_en = row['Payment Proof Documents in English'].strip()
    doc_sc = row['Payment Proof Documents in Chinese'].strip()
    mandatory = row['Mandatory'].strip()
    upload_method = row['Upload Method'].replace('\n', ' ').strip()
    doc_code = row['Doc Code for printing portal'].strip()
    source_type = row['Source Type for Chorus'].strip()
    filename_rule = row['Filename(pass to upload api)'].strip()

    # 记录最近的非空条件
    if pm: last_pm = pm
    if amt: last_amt = amt
    if hr: last_hr = hr

    # 新规则起始
    is_new_rule = False
    if i == 0 or (pm or amt or hr):
        is_new_rule = True
        last_rule = {
            'paymentMethod': pm,
            'amountRange': amt,
            'highRisk': hr,
            'documents': []
        }
        rules.append(last_rule)
    # 材料内容
    if doc_en or doc_sc:
        doc = {
            'id': source_type or doc_code or 'unknown',
            'docCode': doc_code,
            'description': {'en': doc_en, 'sc': doc_sc},
            'mandatory': True if mandatory == 'Y' else False,
            'uploadMethod': upload_method,
            'sourceType': source_type,
            'filenameRule': filename_rule
        }
        if last_rule:
            last_rule['documents'].append(doc)

with open('uploadInfoMapping_full.json', 'w', encoding='utf-8') as f:
    json.dump({'paymentProofRules': rules}, f, ensure_ascii=False, indent=2)